﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Xml;


namespace 学生成绩管理系统
{
    class DataAccess
    {
        public static string ConnectionSttring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        public bool ExecuteSQL(string sql)
        {
            SqlConnection cn = new SqlConnection(DataAccess.ConnectionSttring);
            SqlCommand cm = new SqlCommand(sql, cn);
            try
            {
                cn.Open();
                cm.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                cn.Close();
                cn.Dispose();
                cm.Dispose();
            }
        }
        public SqlDataReader GetReader(string sql)
        {
            SqlConnection cn = new SqlConnection(DataAccess.ConnectionSttring);
            SqlCommand cm = new SqlCommand(sql, cn);
            SqlDataReader dr = null;
            try
            {
                cn.Open();
                dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                dr.Close();
                cn.Dispose();
                cm.Dispose();
                throw new Exception(ex.ToString());
            }
            return dr;
        }
        public DataSet GetDataSet(string sql, string biao)
        {
            DataSet ds = new DataSet();
            SqlConnection cn = new SqlConnection(DataAccess.ConnectionSttring);
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            try
            {
                da.Fill(ds, biao);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                cn.Close();
                cn.Dispose();
                da.Dispose();
            }
            return ds;
        }
        public int GetCount(string sql)
        {
            SqlConnection cn = new SqlConnection(DataAccess.ConnectionSttring);
            SqlCommand cm = new SqlCommand(sql, cn);
            try
            {
                cn.Open();
                int count = (int)cm.ExecuteScalar();
                return count;
            }
            catch( Exception ee)
            {
                ee.ToString();
                return 0;
            }
            finally
            {
                cn.Close();
                cn.Dispose();
                cm.Dispose();
            }
        }
        //验证用户是否合法登录
        public bool CheckAdmin(string strname, string strpwd)
        {
            string sql;
            strname = NameReplace(strname);
            sql = "select   count(*)   from  用户表  where   用户名= '"
                + strname + "'   and  用户密码= '" + strpwd + "' ";
            if (GetCount(sql) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        private string NameReplace(string strname)
        {
            return strname;
            //throw new Exception("The method or operation is not implemented.");
        }
        public static int Result(string stuid, string kcid)
        {

            string sql = @"select * from 成绩表 
where 学号='"+stuid+"'and 课程编号='"+kcid+"'";
            SqlConnection con = new SqlConnection(ConnectionSttring);
            SqlCommand cmd = new SqlCommand(sql, con);
            con.Open();
            try
            {
                return (int)cmd.ExecuteScalar();

            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Close();
            }

        }

        public static int ReadData(string name, string pwd)
        {
           
            string sql = @"select * from 用户表 where
                 用户名='"+name+"' and 用户密码 = '"+pwd+"'";
            SqlConnection con = new SqlConnection(ConnectionSttring);
            SqlCommand cmd = new SqlCommand(sql,con);
            con.Open();
            try
            {
              return  (int)cmd.ExecuteScalar();
               
            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Close();
            }
          
        }
        public static int UpdateData(string name, string newpwd)
        {
            string sql = @"update 用户表 set 
            用户密码='"+newpwd +"' where 用户名='"+name +"'";
           // SqlParameter
            SqlConnection con = new SqlConnection(ConnectionSttring);
            SqlCommand cmd = new SqlCommand(sql, con);
            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result;
        }
    }
}
